package com.example.dao.impl;

import com.example.dao.TopicDao;
import com.example.pojo.Topic;
import com.example.utils.DBConnection;
import org.springframework.stereotype.Repository;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.*;

@Repository("topicDao")
public class TopicDaoImpl implements TopicDao {

    public List<Map<String,Object>> getHotTopicList() {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        List<Map<String,Object>> topicList = new ArrayList<>();
        try {
            conn = DBConnection.getConnection();
            String sql = "select nickname,t_id tId,topic.u_id uId,type,avatar,title,content,publish_time publishTime," +
                    "likes," +
                    "collection_times collectionTimes,real_status realStatus from topic,user where check_status = 1 " +
                    "and topic.u_id = " +
                    "user.u_Id order by likes desc,collectionTimes desc " +
                    "limit 10";
            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();

            while(rs.next()) {
                Map<String,Object> map = new HashMap<>();
                map.put("nickname",rs.getString("nickname"));
                map.put("tId",rs.getInt("tId"));
                map.put("uId",rs.getInt("uId"));
                map.put("avatar",rs.getString("avatar"));
                map.put("type",rs.getString("type"));
                map.put("title",rs.getString("title"));
                map.put("content",rs.getString("content"));
                map.put("publishTime",rs.getString("publishTime").substring(0,16));
                map.put("likes",rs.getInt("likes"));
                map.put("collectionTimes",rs.getInt("collectionTimes"));
                map.put("realStatus",rs.getString("realStatus"));
                topicList.add(map);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        DBConnection.close(rs,ps,conn);
        return topicList;
    }

    public List<Map<String,Object>> getNewTopicList() {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        List<Map<String,Object>> topicList = new ArrayList<>();
        try {
            conn = DBConnection.getConnection();
            String sql = "select nickname,t_id tId,topic.u_id uId,avatar,type,title,content,publish_time publishTime," +
                    "likes," +
                    "collection_times collectionTimes,real_status realStatus from topic,user where check_status = 1 " +
                    "and topic.u_id = " +
                    "user.u_Id order by publishTime desc " +
                    "limit 10";
            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();

            while(rs.next()) {
                Map<String,Object> map = new HashMap<>();
                map.put("nickname",rs.getString("nickname"));
                map.put("tId",rs.getInt("tId"));
                map.put("uId",rs.getInt("uId"));
                map.put("avatar",rs.getString("avatar"));
                map.put("type",rs.getString("type"));
                map.put("title",rs.getString("title"));
                map.put("content",rs.getString("content"));
                map.put("publishTime",rs.getString("publishTime").substring(0,16));
                map.put("likes",rs.getInt("likes"));
                map.put("collectionTimes",rs.getInt("collectionTimes"));
                map.put("realStatus",rs.getString("realStatus"));
                topicList.add(map);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        DBConnection.close(rs,ps,conn);
        return topicList;
    }

    public List<Map<String,Object>> getNiceTopicList() {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        List<Map<String,Object>> topicList = new ArrayList<>();
        try {
            conn = DBConnection.getConnection();
            String sql = "select nickname,t_id tId,topic.u_id uId,avatar,type,title,content,publish_time publishTime," +
                    "likes," +
                    "collection_times collectionTimes,real_status realStatus from topic,user where check_status = 1 " +
                    "and topic.u_id = " +
                    "user.u_Id order by collectionTimes desc,likes desc " +
                    "limit 10";
            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();

            while(rs.next()) {
                Map<String,Object> map = new HashMap<>();
                map.put("nickname",rs.getString("nickname"));
                map.put("tId",rs.getInt("tId"));
                map.put("uId",rs.getInt("uId"));
                map.put("avatar",rs.getString("avatar"));
                map.put("type",rs.getString("type"));
                map.put("title",rs.getString("title"));
                map.put("content",rs.getString("content"));
                map.put("publishTime",rs.getString("publishTime").substring(0,16));
                map.put("likes",rs.getInt("likes"));
                map.put("collectionTimes",rs.getInt("collectionTimes"));
                map.put("realStatus",rs.getString("realStatus"));
                topicList.add(map);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        DBConnection.close(rs,ps,conn);
        return topicList;
    }

    public List<Map<String,Object>> getSpecificTypeList(String type) {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        List<Map<String,Object>> specificList = new ArrayList<>();
        try {
            conn = DBConnection.getConnection();
            String sql = "select nickname,t_id tId,topic.u_id uId,avatar,type,title,content,publish_time publishTime," +
                    "likes," +
                    "collection_times collectionTimes,real_status realStatus from topic,user where type = ? and " +
                    "check_status = 1 and topic" +
                    ".u_id = user.u_id";
            ps = conn.prepareStatement(sql);
            ps.setString(1,type);
            rs = ps.executeQuery();

            while(rs.next()) {
                Map<String,Object> map = new HashMap<>();
                map.put("nickname",rs.getString("nickname"));
                map.put("tId",rs.getInt("tId"));
                map.put("uId",rs.getInt("uId"));
                map.put("avatar",rs.getString("avatar"));
                map.put("type",rs.getString("type"));
                map.put("title",rs.getString("title"));
                map.put("content",rs.getString("content"));
                map.put("publishTime",rs.getString("publishTime").substring(0,16));
                map.put("likes",rs.getInt("likes"));
                map.put("collectionTimes",rs.getInt("collectionTimes"));
                map.put("realStatus",rs.getString("realStatus"));
                specificList.add(map);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        DBConnection.close(rs,ps,conn);
        return specificList;
    }

    public List<Map<String,Object>> getGoodTopicList(String goodType) {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        List<Map<String,Object>> goodTopicList = new ArrayList<>();
        try {
            conn = DBConnection.getConnection();
            String sql = null;
            //0是精品，收藏量
            if("0".equals(goodType)) {
                sql = "select nickname,t_id tId,topic.u_id uId,avatar,type,title,content,publish_time publishTime," +
                        "likes," +
                        "collection_times collectionTimes,real_status realStatus from topic,user where check_status = 1 " +
                        "and topic.u_id = " +
                        "user.u_id order by collectionTimes desc,likes desc " +
                        "limit 30";
            } else {
                sql = "select nickname,t_id tId,topic.u_id uId,avatar,type,title,content,publish_time publishTime," +
                        "likes," +
                        "collection_times collectionTimes,real_status realStatus from topic,user where check_status = 1 " +
                        "and topic.u_id = " +
                        "user.u_id order by likes desc,collectionTimes desc " +
                        "limit 30";
            }
            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();

            while(rs.next()) {
                Map<String,Object> map = new HashMap<>();
                map.put("nickname",rs.getString("nickname"));
                map.put("tId",rs.getInt("tId"));
                map.put("uId",rs.getInt("uId"));
                map.put("avatar",rs.getString("avatar"));
                map.put("type",rs.getString("type"));
                map.put("title",rs.getString("title"));
                map.put("content",rs.getString("content"));
                map.put("publishTime",rs.getString("publishTime").substring(0,16));
                map.put("likes",rs.getInt("likes"));
                map.put("collectionTimes",rs.getInt("collectionTimes"));
                map.put("realStatus",rs.getString("realStatus"));
                goodTopicList.add(map);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        DBConnection.close(rs,ps,conn);
        return goodTopicList;
    }

    public List<Map<String,Object>> getCommentList(Integer tId) {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        List<Map<String,Object>> commentList = new ArrayList<>();
        try {
            conn = DBConnection.getConnection();
            String sql = "select comment.ct_id ctId,comment.u_id comUId,comment.content comContent,comment" +
                    ".publish_time comPublishTime,comment.likes comLikes,comment.real_status comRealStatus,user" +
                    ".nickname,user.avatar " +
                    "from topic,comment,user " +
                    "where topic.t_id = ? and topic.t_id = comment.t_id and comment.check_status = 1 and comment.u_id" +
                    " = user.u_id " +
                    "order by comment.publish_time desc;";
            ps = conn.prepareStatement(sql);
            ps.setInt(1,tId);
            rs = ps.executeQuery();

            while(rs.next()) {
                Map<String,Object> map = new HashMap<>();
                map.put("ctId",rs.getInt("ctId"));
                map.put("comUId",rs.getInt("comUId"));
                map.put("comContent",rs.getString("comContent"));
                map.put("comPublishTime",rs.getString("comPublishTime"));
                map.put("comLikes",rs.getString("comLikes"));
                map.put("comRealStatus",rs.getString("comRealStatus"));
                map.put("nickname",rs.getString("nickname"));
                map.put("avatar",rs.getString("avatar"));
                commentList.add(map);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        DBConnection.close(rs,ps,conn);
        return commentList;
    }

    public List<Map<String,Object>> getSearchList(String words) {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        List<Map<String,Object>> searchList = new ArrayList<>();
        try {
            conn = DBConnection.getConnection();
            String sql = "select nickname,t_id tId,topic.u_id uId,avatar,type,title,content,publish_time publishTime," +
                    "likes,collection_times collectionTimes,real_status realStatus " +
                    "from topic,user " +
                    "where check_status = 1 and topic.u_id = " +
                    "user.u_id and (topic.title like ? or topic.content like ?) order by publish_time desc";

            ps = conn.prepareStatement(sql);
            ps.setString(1,"%" + words +"%");
            ps.setString(2,"%" + words +"%");
            rs = ps.executeQuery();

            while(rs.next()) {
                Map<String,Object> map = new HashMap<>();
                map.put("nickname",rs.getString("nickname"));
                map.put("tId",rs.getInt("tId"));
                map.put("uId",rs.getInt("uId"));
                map.put("avatar",rs.getString("avatar"));
                map.put("type",rs.getString("type"));
                map.put("title",rs.getString("title"));
                map.put("content",rs.getString("content"));
                map.put("publishTime",rs.getString("publishTime").substring(0,16));
                map.put("likes",rs.getInt("likes"));
                map.put("collectionTimes",rs.getInt("collectionTimes"));
                map.put("realStatus",rs.getString("realStatus"));
                searchList.add(map);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        DBConnection.close(rs,ps,conn);
        return searchList;
    }



    public Integer selectIsLike(Integer uId,Integer tId) {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        int count = -1;
        try {
            conn = DBConnection.getConnection();
            String sql = "select count(*) likes from topic_likes where u_id = ? and t_id = ?";
            ps = conn.prepareStatement(sql);
            ps.setInt(1,uId);
            ps.setInt(2,tId);
            rs = ps.executeQuery();
            while(rs.next()) {
                count = rs.getInt("likes");
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        DBConnection.close(rs,ps,conn);
        return count;
    }

    public Integer saveLike(Integer uId,Integer tId,Boolean isLike) {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        int count = -1;

        try {
            conn = DBConnection.getConnection();
            conn.setAutoCommit(false);
            String sql = null;

            if(this.selectIsLike(uId,tId) == 1) {
                if(!isLike) {
                    sql = "delete from topic_likes where u_id = ? and t_id = ?";
                    ps = conn.prepareStatement(sql);
                    ps.setInt(1,uId);
                    ps.setInt(2,tId);
                    count = ps.executeUpdate();
                } else {
                    sql = "update topic_likes set publish_time = ? where u_id = ? and t_id = ?";
                    SimpleDateFormat sdf = new SimpleDateFormat("yyyy:MM:dd:HH:mm:ss");
                    ps = conn.prepareStatement(sql);
                    ps.setString(1,sdf.format(new Date()));
                    ps.setInt(2,uId);
                    ps.setInt(3,tId);
                    count = ps.executeUpdate();
                }
            } else {
                if(isLike) {
                    sql = "insert into topic_likes(u_id,t_id) values (?,?)";
                    ps = conn.prepareStatement(sql);
                    ps.setInt(1,uId);
                    ps.setInt(2,tId);
                    count = ps.executeUpdate();
                }
            }

            ps = conn.prepareStatement(sql);
            conn.commit();
        } catch (Exception e) {
            try {
                conn.rollback();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
            e.printStackTrace();
        }
        DBConnection.close(rs,ps,conn);
        return count;
    }

    public Integer selectIsCollection(Integer uId,Integer tId) {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        int count = -1;
        try {
            conn = DBConnection.getConnection();
            String sql = "select count(*) collection from collection where u_id = ? and t_id = ?";
            ps = conn.prepareStatement(sql);
            ps.setInt(1,uId);
            ps.setInt(2,tId);
            rs = ps.executeQuery();
            while(rs.next()) {
                count = rs.getInt("collection");
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        DBConnection.close(rs,ps,conn);
        return count;
    }

    public Integer saveCollection(Integer uId,Integer tId,Boolean isCollection) {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        int count = -1;

        try {
            conn = DBConnection.getConnection();
            conn.setAutoCommit(false);
            String sql = null;

            if(this.selectIsCollection(uId,tId) == 1) {
                if(!isCollection) {
                    sql = "delete from collection where u_id = ? and t_id = ?";
                    ps = conn.prepareStatement(sql);
                    ps.setInt(1,uId);
                    ps.setInt(2,tId);
                    count = ps.executeUpdate();
                } else {
                    sql = "update collection set publish_time = ? where u_id = ? and t_id = ?";
                    SimpleDateFormat sdf = new SimpleDateFormat("yyyy:MM:dd:HH:mm:ss");
                    ps = conn.prepareStatement(sql);
                    ps.setString(1,sdf.format(new Date()));
                    ps.setInt(2,uId);
                    ps.setInt(3,tId);
                    count = ps.executeUpdate();
                }
            } else {
                if(isCollection) {
                    sql = "insert into collection(u_id,t_id) values (?,?)";
                    ps = conn.prepareStatement(sql);
                    ps.setInt(1,uId);
                    ps.setInt(2,tId);
                    count = ps.executeUpdate();
                }
            }

            ps = conn.prepareStatement(sql);
            conn.commit();
        } catch (Exception e) {
            try {
                conn.rollback();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
            e.printStackTrace();
        }
        DBConnection.close(rs,ps,conn);
        return count;
    }

    public Integer insertTopic(Integer uId,String title,String type,String realStatus,String content) {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        int count = -1;
        try {
            conn = DBConnection.getConnection();
            String sql = "insert into topic(u_id,title,type,real_status,content) values (?,?,?,?,?)";
            ps = conn.prepareStatement(sql);
            ps.setInt(1,uId);
            ps.setString(2,title);
            ps.setString(3,type);
            ps.setString(4,realStatus);
            ps.setString(5,content);
            count = ps.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        }
        DBConnection.close(rs,ps,conn);
        return count;
    }
}
